Imports System.Data.SqlClient
Imports system.Security.Cryptography
Imports System.IO
Imports System.Text

Friend Class DBScanFields
    Inherits DBBase

    Private moServer As SQLAutoDocLib.Server
    Private moDatabase As SQLAutoDocLib.Database
    Private moLogin As SQLAutoDocLib.Login
    Private moTable As SQLAutoDocLib.Table

    Public Sub New( _
                ByVal oServer As SQLAutoDocLib.Server, _
                ByVal oDatabase As SQLAutoDocLib.Database, _
                ByVal oLogin As SQLAutoDocLib.Login, _
                ByVal oTable As SQLAutoDocLib.Table)

        moServer = oServer
        moDatabase = oDatabase
        moLogin = oLogin
        moTable = oTable
    End Sub

    Public Sub Scan( _
                ByRef oFieldList As ArrayList, _
                ByRef sHash As String)

        oFieldList = ScanForFields()
        sHash = ComputeSHA1(oFieldList)
    End Sub

    Private Function ScanForFields() As ArrayList
        Dim oList As New ArrayList

        Dim sSQL As String = "select"
        sSQL = sSQL & "     c.colid,"
        sSQL = sSQL & "     c.name 'ColumnName',"
        sSQL = sSQL & "     t.name 'FieldType',"
        sSQL = sSQL & "     c.length 'ColumnSize',"
        sSQL = sSQL & "     convert(char(5),c.prec) 'ColumnPrecision',"
        sSQL = sSQL & "     convert(char(5),c.scale) 'ColumnScale',"
        sSQL = sSQL & "     c.status 'FieldStatusIndicators',"
        sSQL = sSQL & "     c.colid 'FieldOrder',"
        sSQL = sSQL & "     c.isnullable 'IsNullable',"
        sSQL = sSQL & "     o.text 'Default',"
        sSQL = sSQL & "     q.text 'CheckConstraint'"
        sSQL = sSQL & " from "
        sSQL = sSQL & " dbo.sysobjects a "
        sSQL = sSQL & "                  join dbo.syscolumns c on c.id=a.id"
        sSQL = sSQL & "                  join dbo.systypes t on t.xtype=c.xtype"
        sSQL = sSQL & "                                         and t.xusertype=c.xusertype"
        sSQL = sSQL & "                                         and t.name <> 'sysname'"
        sSQL = sSQL & "                  join dbo.sysusers b on b.uid=a.uid"
        sSQL = sSQL & "                  left outer join dbo.sysobjects n on n.id=c.cdefault"
        sSQL = sSQL & "                  left outer join dbo.syscomments o on o.id=n.id"
        sSQL = sSQL & "                  left outer join dbo.sysobjects p on p.id=c.domain"
        sSQL = sSQL & "                  left outer join dbo.syscomments q on q.id=p.id			"
        sSQL = sSQL & "  where"
        sSQL = sSQL & "     a.type='U'"
        sSQL = sSQL & "     and a.name=" & ValueToDBValue(moTable.Name)
        sSQL = sSQL & "     and b.name=" & ValueToDBValue(moLogin.Login)
        sSQL = sSQL & "  order by"
        sSQL = sSQL & "     a.name,"
        sSQL = sSQL & "     c.colid"

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL, moDatabase.DecryptedConnectionString)

        Dim oFactory As New Field_Factory

        For Each oRow As DataRow In oData.Rows
            Dim oField As New SQLAutoDocLib.Field
            With oField
                .TableID = moTable.ID
                .Name = oRow.Item("ColumnName")
                .ColID = oRow.Item("colid")
                .Type = oRow.Item("FieldType")
                .Size = oRow.Item("ColumnSize")
                .Precision = NullToNumber(oRow.Item("ColumnPrecision"))
                .Scale = NullToNumber(oRow.Item("ColumnScale"))
                .Order = oRow.Item("FieldOrder")
                .IsNotNull = oRow.Item("IsNullable")
                .IsIdentity = oRow.Item("FieldStatusIndicators")
                .DefaultValue = NullToString(oRow.Item("Default"))
                .Constraint = NullToString(oRow.Item("CheckConstraint"))
            End With

            oList.Add(oField)
        Next

        Return oList
    End Function

    Private Function ComputeSHA1(ByVal oList As ArrayList) As String
        Dim sAllFields As New stringbuilder

        For Each oField As SQLAutoDocLib.Field In oList
            sAllFields.Append(oField.SerializeMe)
        Next

        Dim oCrypto As New JCO
        Return oCrypto.GetHash(sAllFields.ToString)
    End Function
End Class
